import sqlalchemy
from sqlalchemy import Table, Column, Integer, String, MetaData, Date, ForeignKey, table
from sqlalchemy.orm import *

class ZHIHUDB:
    # 初始化
    def __init__(self, server:str, db:str, uid:str, pwd:str) -> None:
        self.__server = server
        self.__db = db
        self.__uid = uid
        self.__pwd = pwd
        self.__tables = dict()

    # 连接数据库
    def connect(self) -> None:
        self.__engine = sqlalchemy.create_engine(
            f"mysql+pymysql://{self.__uid}:{self.__pwd}@{self.__server}/{self.__db}"
        )
        self.__conn = self.__engine.connect()
        self.__meta = MetaData(bind=self.__engine)
    
    def close(self) -> None:
        self.__conn.close()

    # 删除所有表
    def drop_all_tables(self) -> None:
        self.__meta.drop_all()

    # 创建所有表
    def create_tables(self) -> None:
        #创建个人基本属性信息表
        self.__tables['Personal_base_info'] = Table (
            'Personal_base_info', self.__meta,
            Column('From_name', String(255)),
            Column('Name', String(255)),
            Column('Sex', String(255)),
            Column('One_intro', String(255)),
            Column('Live_place', String(255)),
            Column('Indus', String(255)),
            Column('Occup', String(255)),
            Column('Full_intro', String(2056))
        )

        #创建社交关系信息表
        # 关注人
        self.__tables['Following_base_info'] = Table (
            'Following_base_info', self.__meta,
            Column('From_name', String(255)),
            Column('Name', String(255)),
            Column('Url', String(255)),
            Column('Question_num', Integer),
            Column('Articles_num', Integer),
            Column('Follower_num', Integer)
        )

        # 粉丝
        self.__tables['Follower_base_info'] = Table (
            'Follower_base_info', self.__meta,
            Column('From_name', String(255)),
            Column('Name', String(255)),
            Column('Url', String(255)),
            Column('Question_num', Integer),
            Column('Articles_num', Integer),
            Column('Follower_num', Integer)
        )

        #动态信息数据库
        # 回答问题信息
        self.__tables['Answer_info'] = Table (
            'Answer_info', self.__meta,
            Column('From_name', String(255)),
            Column('Title', String(255)),
            Column('Time', String(255)),
            Column('Comment', String(2056)),
            Column('Comment_num', Integer),
            Column('Vote_num', Integer),
            Column('Question_id', String(255))
        )

        # 提问信息
        self.__tables['Quiz_info'] = Table (
            'Quiz_info', self.__meta,
            Column('From_name', String(255)),
            Column('Title', String(255)),
            Column('Time', String(255)),
            Column('Comment', String(2056)),
            Column('Comment_num', Integer),
            Column('Follow_num', Integer),
            Column('Question_id', String(255))
        )

        # 每个贴文的信息（前十条评论信息）
        self.__tables['Post_info'] = Table (
            'Post_info', self.__meta,
            Column('From_name', String(255)),
            Column('Title', String(255)),
            Column('Name', String(255)),
            Column('User_id', String(255)),
            Column('Comment_time', String(255)),
            Column('Comment', String(2056)),
            Column('Vote_num', Integer),
            Column('Question_id', String(255))
        )

        self.__meta.create_all()

    # 个人信息表部分---------------------------
    # 填充个人信息表
    def populate_personal_table(self, personal_info: list) -> None:
        self.__conn.execute(self.__tables['Personal_base_info'].insert(), personal_info)

    # 获取个人信息
    def get_personal_info(self, name: str) -> None:
        sql = "SELECT * FROM personal_base_info WHERE From_name='{}';".format(name)
        result = self.__conn.execute(sql).fetchall()
        return result
    
    # 社交信息表部分---------------------------
    # 填充关注人表
    def populate_following_table(self, following_info: list) -> None:
        self.__conn.execute(self.__tables['Following_base_info'].insert(), following_info)

    # 查找关注人信息
    def get_following_info(self, name: str) -> None:
        sql = "SELECT * FROM following_base_info WHERE From_name='{}';".format(name)
        result = self.__conn.execute(sql).fetchall()
        if len(result) > 10:
            result = result[0:10]
        return result

    # 填充粉丝表
    def populate_follower_table(self, follower_info: list) -> None:
        self.__conn.execute(self.__tables['Follower_base_info'].insert(), follower_info)

    # 查找粉丝信息
    def get_follower_info(self, name: str) -> None:
        sql = "SELECT * FROM follower_base_info WHERE From_name='{}';".format(name)
        result = self.__conn.execute(sql).fetchall()
        if len(result) > 10:
            result = result[0:10]
        return result

    # 动态信息表部分---------------------------
    # 填充回答问题表
    def populate_answer_table(self, answer_info: list) -> None:
        self.__conn.execute(self.__tables['Answer_info'].insert(), answer_info)

    # 查找回答问题表
    def get_answer_info(self, name:str) -> None:
        sql = "SELECT * FROM answer_info WHERE From_name='{}';".format(name)
        result = self.__conn.execute(sql).fetchall()
        if len(result) > 10:
            result = result[-10:]
        return list(set(result))

    # 填充提问信息表
    def populate_quiz_table(self, quiz_info: list) -> None:
        self.__conn.execute(self.__tables['Quiz_info'].insert(), quiz_info)

    # 查找提问信息表
    def get_quiz_info(self, name:str) -> None:
        sql = "SELECT * FROM quiz_info WHERE From_name='{}';".format(name)
        result = self.__conn.execute(sql).fetchall()
        if len(result) > 10:
            result = result[-10:]
        return result

    # 填充贴文信息表
    def populate_post_table(self, post_info:list) -> None:
        self.__conn.execute(self.__tables['Post_info'].insert(), post_info)

    # 查找贴文信息表
    def get_post_info(self, question_id: str) -> None:
        sql = "SELECT * FROM post_info WHERE Question_id='{question_id}';".format(question_id=question_id)
        result = self.__conn.execute(sql).fetchall()
        if len(result) > 10:
            result = result[-10:]
        return result

if __name__ == "__main__":
    DB = ZHIHUDB("localhost", "xiongdb", "root", "280513")
    DB.connect()
    DB.create_tables()
    # DB.drop_all_tables()
    a = DB.get_answer_info('min-xi-cheng-nian-tatan')
    print(a)
    DB.close()
